package com.dao.impl;

import com.dao.CategoryDao;
import com.db.ConnectionFactory;
import com.entry.Goods;
import com.util.PageModel;
import java.sql.*;
import java.util.ArrayList;

public class CategoryDaoImpl implements CategoryDao {
    @Override
    public PageModel<Goods> findPc(int pageSize, int pageNo) {
        ArrayList<Goods> list = new ArrayList<Goods>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select gid,gname from goods";
        PageModel<Goods> pm = new PageModel<Goods>(pageSize, pageNo, total(sql));
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql+" limit " + (pm.getPageNo() - 1) * pm.getPageSize() + "," + pm.getPageSize());
            rs = pre.executeQuery();
            while (rs.next()){
                Goods goods = new Goods();
                goods.setGid(rs.getString(1));
                goods.setGname(rs.getString(2));
                list.add(goods);
            }
            pm.setData(list);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (rs != null)
                    rs.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }

    public int total(String sql) {
        int recordCount = 0;
        String countsql = "select count(*) "
                + sql.substring(sql.indexOf("from"), sql.length());

        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(countsql);
            if (rs.next()) {
                recordCount = rs.getInt(1);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if (rs != null){rs.close();}
                if (stmt != null){stmt.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return recordCount;
    }

    @Override
    public int add(Goods goods) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "insert into goods(gid,gname) values(?,?)";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,goods.getGid());
            pre.setString(2,goods.getGname());
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    @Override
    public Goods finId(String id) {
        Goods goods = null;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        String sql = "select gid,gname from goods where gid=? limit 1";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,id);
            rs = pre.executeQuery();
            if(rs.next()){
                goods = new Goods();
                goods.setGid(rs.getString(1));
                goods.setGname(rs.getString(2));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (rs != null){rs.close();}
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return goods;
    }

    @Override
    public int Update(Goods goods) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "update goods set gname=? where gid=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(2,goods.getGid());
            pre.setString(1,goods.getGname());
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try{
                if (pre != null){pre.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return row;
    }

    @Override
    public int delete(String id) {
        int row = 0;
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "delete from goods where gid=?";
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,id);
            row = pre.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return row;
    }
}
